﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Tools
{
    public class Utility
    {
        public static DataTable schema_collections(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name], '' schemat ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   s.name [Name], sch.name schemat ");
                    sb.AppendLine("FROM  sys.xml_schema_collections s ");
                    sb.AppendLine("LEFT JOIN sys.schemas sch on s.schema_id = sch.schema_id");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }

            }
            catch (Exception exception)
            {
                // Logger
            }

            return null;
        }

        public static DataTable authorizations(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.database_principals ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                // Logger
            }

            return null;
        }
    }
}
